package com.bsoft.gol.hbs.dao;

import com.bsoft.gol.hbs.bean.dto.HealthInfoInteractionCountUpdateDTO;
import com.bsoft.gol.hbs.bean.qo.HealthInfoCommentDashboardQo;
import com.bsoft.gol.hbs.bean.qo.MyArticleListQo;
import ctd.persistence.annotation.DAOMethod;
import ctd.persistence.annotation.DAOParam;
import ctd.persistence.support.hibernate.template.AbstractHibernateStatelessResultAction;
import ctd.persistence.support.hibernate.template.HibernateSessionTemplate;
import ctd.persistence.support.hibernate.template.HibernateStatelessResultAction;
import hcn.base.healthinfo.HealthInfo;
import hcn.base.healthinfo.vo.CommentDashboardVO;
import org.atag.dao.action.QoHibernateStatelessResultAction;
import org.atag.dao.model.SimplePageInfo;
import org.atag.dao.ext.HibernateExtendDAO;
import org.hibernate.Query;
import org.hibernate.StatelessSession;
import org.hibernate.criterion.CriteriaSpecification;
import com.bsoft.gol.hbs.utils.CommonUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 健康资讯数据库访问层
 *
 * @author linyi
 * @date 2021/10/9 16:35
 */
public abstract class HealthInfoDAO extends HibernateExtendDAO<HealthInfo> {


    /**
     * 根据主键查询健康资讯
     */
    @DAOMethod(sql = "from HealthInfo where articleId=:articleId")
    public abstract HealthInfo getHealthInfoById(@DAOParam("articleId") String articleId);

    /**
     * 根据主键查询健康资讯列表
     */
    @DAOMethod(sql = "from HealthInfo where articleId IN (:articleIds)")
    public abstract List<HealthInfo> findHealthInfoListByIds(@DAOParam("articleIds") List<String> articleId);

    @DAOMethod(sql = "select count(1) from HealthInfo where category=:category ")
    public abstract Long getCountForArticle(@DAOParam("category") String category);

    /**
     * 查询医生是否存在已发布的文章
     *
     * @param doctorId
     * @return long
     * @throws
     * @author tongtp
     * @date 2023/9/1 16:50
     */
    @DAOMethod(sql = "select count(1) from HealthInfo where doctorId = :doctorId and articleStatus = 3 ")
    public abstract long getCountByDoctorId(@DAOParam("doctorId") String doctorId);


    /**
     * 插入健康资讯
     */
//    public void insert(HealthInfo insert) {
//        HibernateStatelessResultAction<Void> action = new AbstractHibernateStatelessResultAction<Void>() {
//            @Override
//            public void execute(StatelessSession statelessSession) {
//                String sql = "INSERT INTO health_info " +
//                        "(article_id,title,title_image,category,category_name,doctor_id,author_id,author_name,disease_group_list,disease_group_name," +
//                        "person_group_list,person_group_name,file_list,article_content,article_status,gmt_create,gmt_modified)" +
//                        "VALUES" +
//                        " (:articleId,:title,:titleImage,:category,:categoryName,:doctorId,:authorId,:authorName,:diseaseGroupList,:diseaseGroupName," +
//                        ":personGroupList,:personGroupName,:fileList,:articleContent,:articleStatus,:gmtCreate,:gmtModified)";
//                Query query = statelessSession.createSQLQuery(sql);
//                query.setParameter("articleId", insert.getArticleId());
//                query.setParameter("title", insert.getTitle());
//                query.setParameter("titleImage", insert.getTitleImage());
//                query.setParameter("category", insert.getCategory());
//                query.setParameter("categoryName", insert.getCategoryName());
//                query.setParameter("doctorId", insert.getDoctorId());
//                query.setParameter("authorId", insert.getAuthorId());
//                query.setParameter("authorName", insert.getAuthorName());
//                query.setParameter("diseaseGroupList", insert.getDiseaseGroupList());
//                query.setParameter("diseaseGroupName", insert.getDiseaseGroupName());
//                query.setParameter("personGroupList", insert.getPersonGroupList());
//                query.setParameter("personGroupName", insert.getPersonGroupName());
//                query.setParameter("fileList", insert.getFileList());
//                query.setParameter("articleContent", insert.getArticleContent());
//                query.setParameter("articleStatus", insert.getArticleStatus());
//                query.setParameter("gmtCreate", insert.getGmtCreate());
//                query.setParameter("gmtModified", insert.getGmtModified());
//                query.executeUpdate();
//            }
//        };
//        HibernateSessionTemplate.instance().execute(action);
//    }


    //更新健康资讯指定字段
//    public void updateHealthInfo(HealthInfo update) {
//        HibernateStatelessResultAction<Void> action = new AbstractHibernateStatelessResultAction<Void>() {
//            @Override
//            public void execute(StatelessSession statelessSession) {
//                String sql = "UPDATE health_info " +
//                        "(article_id,title,title_image,category,category_name,author_id,author_name,disease_group_list," +
//                        "person_group_list,file_list,article_content,gmt_create,gmt_modified)" +
//                        "VALUES" +
//                        " (:articleId,:title,:titleImage,:category,:categoryName,:authorId,:authorName,:diseaseGroupList," +
//                        ":personGroupList,:fileList,:articleContent,:gmtCreate,:gmtModified)" +
//                        ")";
//                Query query = statelessSession.createSQLQuery(sql);
////                query.setParameter("articleId", insert.getArticleId());
////                query.setParameter("title", insert.getTitle());
////                query.setParameter("titleImage", insert.getTitleImage());
////                query.setParameter("category", insert.getCategory());
////                query.setParameter("categoryName", insert.getCategoryName());
////                query.setParameter("authorId", insert.getAuthorId());
////                query.setParameter("authorName", insert.getAuthorName());
////                query.setParameter("diseaseGroupList", insert.getDiseaseGroupList());
////                query.setParameter("personGroupList", insert.getPersonGroupList());
////                query.setParameter("submitTime", insert.getPersonGroupList());
////                query.setParameter("personGroupList", insert.getPersonGroupList());
////                query.setParameter("fileList", insert.getFileList());
////                query.setParameter("articleContent", insert.getArticleContent());
////                query.setParameter("gmtCreate", insert.getGmtCreate());
////                query.setParameter("gmtModified", insert.getGmtModified());
////                query.executeUpdate();
//            }
//        };
//        HibernateSessionTemplate.instance().execute(action);
//    }

    /**
     * 查询患者端健康资讯列表 推荐优先  union连接
     */
    public SimplePageInfo<HealthInfo> getRecommendHealthInfoList(MyArticleListQo request) {
        QoHibernateStatelessResultAction<HealthInfo, MyArticleListQo> action
                = new QoHibernateStatelessResultAction<>(true, request, HealthInfo.class);
        List<String> diseaseGroup = request.getDiseaseGroup();
        List<String> personGroup = request.getPersonGroup();
        String order = "";
        if (request.isDesc()) {
            order = " ORDER BY release_time DESC";
        } else {
            order = " ORDER BY release_time ASC";
        }
        String sql = " SELECT {{" +
                " A.article_id AS articleId," +
                " A.title," +
                " A.title_image AS titleImage," +
                " A.category," +
                " A.category_name AS categoryName," +
                " A.author_id AS authorId," +
                " A.author_name AS authorName," +
                " A.disease_group_list AS diseaseGroupList," +
                " A.person_group_list AS personGroupList," +
                " A.submit_time AS submitTime," +
                " A.release_user_id AS releaseUserId," +
                " A.release_time AS releaseTime," +
                " A.read_count AS readCount," +
                " A.likes_count AS likesCount," +
                " A.comment_count AS commentCount," +
                " A.collection_count AS collectionCount," +
                " A.share_count AS shareCount," +
                " A.article_status AS articleStatus," +
                " A.file_list AS fileList," +
                " A.article_content AS articleContent," +
                " A.gmt_create AS gmtCreate," +
                " A.gmt_modified AS gmtModified" +
                "}} " +
                " FROM ( " +
                " SELECT * FROM (SELECT * FROM health_info WHERE article_status=3";
        action.setSql(sql);
        if (CommonUtils.isNotEmpty(diseaseGroup)) {
            action.addSql(" AND ( " + contactContainsString(diseaseGroup, "disease_group_list") + ")");
        }
        if (CommonUtils.isNotEmpty(personGroup)) {
            action.addSql(" AND ( " + contactContainsString(personGroup, "person_group_list") + ")");
        }
        action.addSql(order);
        action.addSql(" limit 0, 50) t1 ");
        action.addSql(" UNION SELECT * FROM (SELECT * FROM health_info WHERE article_status=3");
        action.addSql(order);
        action.addSql(" limit 0, 50) t1 ");
        action.addSql(" ) AS A");

        HibernateSessionTemplate.instance().execute(action);

        return action.getSimpleResultPage();
    }

    /**
     * 查询健康资讯文章列表
     */
    public SimplePageInfo<HealthInfo> getHealthInfoPage(MyArticleListQo request) {
        QoHibernateStatelessResultAction<HealthInfo, MyArticleListQo> action
                = new QoHibernateStatelessResultAction<>(true, request, HealthInfo.class);
        String sql = " SELECT {{" +
                " article_id AS articleId," +
                " title," +
                " title_image AS titleImage," +
                " category," +
                " category_name AS categoryName," +
                " author_id AS authorId," +
                " author_name AS authorName," +
                " disease_group_list AS diseaseGroupList," +
                " person_group_list AS personGroupList," +
                " submit_time AS submitTime," +
                " release_user_id AS releaseUserId," +
                " release_time AS releaseTime," +
                " read_count AS readCount," +
                " likes_count AS likesCount," +
                " comment_count AS commentCount," +
                " collection_count AS collectionCount," +
                " share_count AS shareCount," +
                " article_status AS articleStatus," +
                " file_list AS fileList," +
                " article_content AS articleContent," +
                " gmt_create AS gmtCreate," +
                " gmt_modified AS gmtModified" +
                "}} " +
                " FROM health_info" +
                " WHERE 1=1";
        action.setSql(sql);
        action.addCondition(" AND category=:category ");
        action.addCondition(" AND author_id=:userId ");
        action.addCondition(" AND author_name LIKE :%authorName% ");
        action.addCondition(" AND article_status=:articleStatus ");
        action.addCondition(" AND article_status in (:articleStatusList) ");
        action.addCondition(" AND article_id in (:articleIds) ");
        action.addCondition(" AND title LIKE :%title%  ");
        //两个列表
        List<String> diseaseGroup = request.getDiseaseGroup();
        List<String> personGroup = request.getPersonGroup();
        //todo sql inject
        if (CommonUtils.isNotEmpty(diseaseGroup)) {
            action.addSql(" AND ( " + contactContainsString(diseaseGroup, "disease_group_list") + ")");
        }
        if (CommonUtils.isNotEmpty(personGroup)) {
            action.addSql(" AND ( " + contactContainsString(personGroup, "person_group_list") + ")");
        }
        if (request.isDesc()) {
            action.addOrder(" ORDER BY release_time DESC");
        }

        HibernateSessionTemplate.instance().execute(action);

        return action.getSimpleResultPage();
    }

    /**
     * 查询健康资讯文章列表
     */
    public SimplePageInfo<HealthInfo> getInfoListForWord(MyArticleListQo request) {
        QoHibernateStatelessResultAction<HealthInfo, MyArticleListQo> action
                = new QoHibernateStatelessResultAction<>(true, request, HealthInfo.class);
        String sql = " SELECT {{" +
                " article_id AS articleId," +
                " title," +
                " title_image AS titleImage," +
                " category," +
                " category_name AS categoryName," +
                " author_id AS authorId," +
                " author_name AS authorName," +
                " disease_group_list AS diseaseGroupList," +
                " person_group_list AS personGroupList," +
                " submit_time AS submitTime," +
                " release_user_id AS releaseUserId," +
                " release_time AS releaseTime," +
                " read_count AS readCount," +
                " likes_count AS likesCount," +
                " comment_count AS commentCount," +
                " collection_count AS collectionCount," +
                " share_count AS shareCount," +
                " article_status AS articleStatus," +
                " file_list AS fileList," +
                " article_content AS articleContent," +
                " gmt_create AS gmtCreate," +
                " gmt_modified AS gmtModified" +
                "}} " +
                " FROM health_info" +
                " WHERE 1=1";
        action.setSql(sql);
        action.addCondition(" AND category=:category ");
        action.addCondition(" AND doctor_id=:userId ");
        action.addCondition(" AND author_name LIKE :%authorName% ");
        action.addCondition(" AND article_status=:articleStatus ");
        action.addCondition(" AND article_status in (:articleStatusList) ");
        action.addCondition(" AND article_id in (:articleIds) ");
        action.addCondition(" AND title LIKE :%title%  ");
        //两个列表
        List<String> diseaseGroup = request.getDiseaseGroup();
        List<String> personGroup = request.getPersonGroup();
        //todo sql inject
        if (CommonUtils.isNotEmpty(diseaseGroup)) {
            action.addSql(" AND ( " + contactContainsString(diseaseGroup, "disease_group_list") + ")");
        }
        if (CommonUtils.isNotEmpty(personGroup)) {
            action.addSql(" AND ( " + contactContainsString(personGroup, "person_group_list") + ")");
        }
        if (request.isDesc()) {
            action.addOrder(" ORDER BY release_time DESC");
        }

        HibernateSessionTemplate.instance().execute(action);

        return action.getSimpleResultPage();
    }

    /**
     * FIND_IN_SET('', xx) OR FIND_IN_SET('', xx) OR FIND_IN_SET('', xx)
     */
    private String contactContainsString(List<String> strings, String field) {
        List<String> collect = strings.stream().map(s -> String.format("FIND_IN_SET('%s', %s)", s, field))
                .collect(Collectors.toList());
        return String.join(" OR ", collect);
    }


    /**
     * 查询留言看板左侧列表
     * SELECT
     * A.article_id AS articleId,
     * A.reply_user_name AS replyUserName,
     * A.reply_time AS replyTime FROM (
     * SELECT
     * article_id,
     * reply_user_name,
     * reply_time
     * FROM
     * health_info_reply
     * WHERE
     * is_read = 0
     * AND p_reply_id IS NULL UNION
     * SELECT
     * article_id,
     * reply_user_name,
     * reply_time
     * FROM
     * health_info_reply
     * WHERE
     * is_read = 0
     * AND target_user_id = '20fd49447b69424cb8154201910b7570'
     * ) AS A
     * LIMIT 1
     */
    public List<CommentDashboardVO> getCommentDashboard(HealthInfoCommentDashboardQo qo) {

        QoHibernateStatelessResultAction<CommentDashboardVO, HealthInfoCommentDashboardQo> action
                = new QoHibernateStatelessResultAction<>(false, qo, CommentDashboardVO.class);
        String builder = " SELECT {{" +
                " article_id AS articleId," +
                " title," +
                " release_time AS releaseTime," +
                " read_count AS readCount," +
                " likes_count AS likesCount," +
                " comment_count AS commentCount," +
                " collection_count AS collectionCount," +
                " share_count AS shareCount," +
                " article_status AS articleStatus" +
                " FROM health_info" +
                " WHERE article_status = 3";
        action.setSql(builder);
        action.addCondition(" AND release_time>:dateStart");
        action.addCondition(" AND author_id=:authorId");
        action.addOrder(" ORDER BY release_time DESC");
        HibernateSessionTemplate.instance().execute(action);
        return action.getResultList();
    }


    /**
     * 更新互动操作的数量
     * read_count,
     * likes_count,
     * comment_count,
     * collection_count,
     * share_count,
     */
    public void updateInteractionCount(HealthInfoInteractionCountUpdateDTO dto) {
        if (CommonUtils.isEmpty(dto.getArticleId())) {
            return;
        }
        if (CommonUtils.isEmpty(dto.getReadCountAdd()) && CommonUtils.isEmpty(dto.getLikesCountAdd())
                && CommonUtils.isEmpty(dto.getCommentCountAdd()) && CommonUtils.isEmpty(dto.getCollectionCountAdd())
                && CommonUtils.isEmpty(dto.getShareCountAdd())) {
            return;
        }
        HibernateStatelessResultAction<Integer> action = new AbstractHibernateStatelessResultAction<Integer>() {
            @Override
            public void execute(StatelessSession statelessSession) {
                StringBuilder hsql = new StringBuilder("UPDATE HealthInfo SET ");
                List<String> conditions = new ArrayList<>();

                if (CommonUtils.isNotEmpty(dto.getReadCountAdd())) {
                    conditions.add(" readCount=readCount + :readCountAdd");
                }
                if (CommonUtils.isNotEmpty(dto.getLikesCountAdd())) {
                    conditions.add(" likesCount=likesCount + :likesCountAdd");
                }
                if (CommonUtils.isNotEmpty(dto.getCommentCountAdd())) {
                    conditions.add(" commentCount=commentCount + :commentCountAdd");
                }
                if (CommonUtils.isNotEmpty(dto.getCollectionCountAdd())) {
                    conditions.add(" collectionCount=collectionCount + :collectionCountAdd");
                }
                if (CommonUtils.isNotEmpty(dto.getShareCountAdd())) {
                    conditions.add(" shareCount=shareCount + :shareCountAdd");
                }
                hsql.append(String.join(", ", conditions));
                hsql.append(" WHERE articleId = :articleId");

                Query query = statelessSession.createQuery(hsql.toString());
                query.setParameter("articleId", dto.getArticleId());
                if (CommonUtils.isNotEmpty(dto.getReadCountAdd())) {
                    query.setParameter("readCountAdd", dto.getReadCountAdd());
                }
                if (CommonUtils.isNotEmpty(dto.getLikesCountAdd())) {
                    query.setParameter("likesCountAdd", dto.getLikesCountAdd());
                }
                if (CommonUtils.isNotEmpty(dto.getCommentCountAdd())) {
                    query.setParameter("commentCountAdd", dto.getCommentCountAdd());
                }
                if (CommonUtils.isNotEmpty(dto.getCollectionCountAdd())) {
                    query.setParameter("collectionCountAdd", dto.getCollectionCountAdd());
                }
                if (CommonUtils.isNotEmpty(dto.getShareCountAdd())) {
                    query.setParameter("shareCountAdd", dto.getShareCountAdd());
//                    sql.append(" share_count=share_count + :shareCount");
                }
                query.executeUpdate();
            }
        };
        HibernateSessionTemplate.instance().execute(action);
    }


    public  Long getReadingVolumeForInfo( List<String> articleIdArr){
        HibernateStatelessResultAction<Long> action = new AbstractHibernateStatelessResultAction<Long>() {
            @Override
            public void execute(StatelessSession ss) {
                StringBuffer stringBuffer = new StringBuffer("select SUM(read_count) readCount from health_info where article_id in :articleIdArr ");
                Query query = ss.createSQLQuery(stringBuffer.toString());
                query.setParameterList("articleIdArr", articleIdArr);
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> dbList = query.list();
                if (CommonUtils.isNotEmpty(dbList)) {
                    setResult(Long.valueOf(String.valueOf(dbList.get(0).get("readCount"))));
                } else {
                    setResult(0L);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }
}
